package com.wzs.dao.impl;

import com.wzs.dao.IUserDao;
import com.wzs.model.Address;
import com.wzs.model.User;
import com.wzs.model.UserCondition;
import com.wzs.util.JdbcUtil;
import com.wzs.util.PageModel;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author miaomiao
 * @version 1.0.0
 * @ClassName UserDao.java
 * @Description IUserDao接口的实现类
 * @createTime 2021-06-19- 11:26:00
 */
public class UserDao implements IUserDao {
    @Override
    public ArrayList<User> getUserByCondition(UserCondition condition) {

        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList<User> users = new ArrayList<>();

        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            //2、执行sql
            String sql = "select * from user where 1=1";
            if (condition.getName() != null && !condition.getName().equals("")) {
                sql += " and name like ?";
            }
            pstm = conn.prepareStatement(sql);

            if (condition.getName() != null && !"".equals(condition.getName())) {
                pstm.setString(1, "%" + condition.getName() + "%");
            }

            //3.获取结果
            rs = pstm.executeQuery();

            //5.读取数据
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String password = rs.getString("password");
                User u = new User(id, name, password, null);
                users.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pstm, rs);
        }
        return users;
    }

    @Override
    public User getUserDetails(String uId) {

        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = new User();

        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            //2、执行sql
            String sql = "SELECT u.id uid,`name`,`password`,a.id aid,province,city,county from `user` u LEFT JOIN address a on u.id = a.u_id where u.id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, uId);

            //3.获取结果
            rs = pstm.executeQuery();

            //5.读取数据
            ArrayList<Address> addresses = new ArrayList<>();
            Integer uid = null;
            String name = null;
            String password = null;
            while (rs.next()) {
                uid = rs.getInt("uid");
                name = rs.getString("name");
                password = rs.getString("password");
                String address_id = rs.getString("aid");
                String province = rs.getString("province");
                String city = rs.getString("city");
                String county = rs.getString("county");
                if (address_id != null) {
                    Address add = new Address(Integer.parseInt(address_id), province, county, city, uid);
                    addresses.add(add);
                }
            }
            if (uid == null) {
                return null;
            }
            user = new User(uid, name, password, addresses);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pstm, rs);
        }
        return user;
    }

    @Override
    public PageModel<User> getUserPageByCondition(UserCondition condition, PageModel<User> pageModel) {

        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList<User> users = new ArrayList<>();

        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();

            //2计算总页数
            String sqlCount = " select count(1) from user where 1=1 ";
            if (condition.getName() != null && !condition.getName().equals("")) {
                sqlCount += " and name like ?";
            }
            pstm = conn.prepareStatement(sqlCount);
            if (condition.getName() != null && !"".equals(condition.getName())) {
                pstm.setString(1, "%" + condition.getName() + "%");
            }

            //3.获取结果
            rs = pstm.executeQuery();
            rs.next();
            int totalRows = rs.getInt(1);   //总条数
            pageModel.setTotalRows(totalRows);

            if (totalRows % pageModel.getPageSize() == 0) {
                pageModel.setPageTotalNum(totalRows / pageModel.getPageSize());
            } else {
                pageModel.setPageTotalNum(totalRows / pageModel.getPageSize() + 1);
            }

            //4获取当前页的数据
            String sqlData = "select * from user where 1=1";
            if (condition.getName() != null && !condition.getName().equals("")) {
                sqlData += " and name like ?";
            }
            sqlData += " limit ?,?";
            pstm = conn.prepareStatement(sqlData);

            if (condition.getName() != null && !"".equals(condition.getName())) {
                pstm.setString(1, "%" + condition.getName() + "%");
                pstm.setInt(2, pageModel.getPageSize() * (pageModel.getPageNum() - 1));
                pstm.setInt(3, pageModel.getPageSize());
            } else {
                pstm.setInt(1, pageModel.getPageSize() * (pageModel.getPageNum() - 1));
                pstm.setInt(2, pageModel.getPageSize());
            }

            rs = pstm.executeQuery();

            //5.读取数据
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String password = rs.getString("password");
                User u = new User(id, name, password, null);
                users.add(u);
            }
            pageModel.setDataList(users);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pstm, rs);
        }
        return pageModel;

    }

    @Override
    public boolean deleteUserById(String uId) {

        Connection conn = null;
        PreparedStatement pstm = null;
        int rs = 0;

        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            //2、执行sql
            String sql = "delete from user where 1=1 AND id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, uId);

            //3.获取结果
            rs = pstm.executeUpdate();

            //4.读取数据
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pstm, null);
        }

        if (rs > 1) {
            return false;
        } else {
            return true;
        }


    }

    @Override
    public boolean saveUser(User user) {
        Connection conn = null;
        PreparedStatement pstm = null;
        int rs = 0;

        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            //2、执行sql
            String sql = "insert into user (name, password) values (?,?)";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, user.getName());
            pstm.setString(2, user.getPassword());

            //3.获取结果
            rs = pstm.executeUpdate();

            //4.读取数据
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pstm, null);
        }

        if (rs > 1) {
            return false;
        } else {
            return true;
        }
    }

    @Override
    public boolean updateUser(User user) {
        Connection conn = null;
        PreparedStatement pstm = null;
        int rs = 0;

        try {
            //1.获取连接
            conn = JdbcUtil.getConnection();
            //2、执行sql
            String sql = "update user set name=?, password=? where id =?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, user.getName());
            pstm.setString(2, user.getPassword());
            pstm.setInt(3, user.getId());

            //3.获取结果
            rs = pstm.executeUpdate();

            //4.读取数据
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pstm, null);
        }

        if (rs > 1) {
            return false;
        } else {
            return true;
        }
    }


}
